/*
 * IQuery.java
 *
 * Created on 15 de noviembre de 2007, 02:47 PM
 */
package ig.formato.gui.app.herramientas;

import ig.formato.dto.ConexionDTO;
import ig.formato.util.IGFormatoException;
import ig.formato.util.MyTableModel;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Vector;
import javax.swing.JTextArea;

/**
 *
 * @author  jsanchez
 */
public class IQuery extends javax.swing.JInternalFrame {

    /** Creates new form IQuery */
    ig.formato.dao.ConexionDao conexion = new ig.formato.dao.ConexionDao();

    public IQuery() {

        initComponents();
        Collection c = new ArrayList();
        try {
            c = conexion.buscarTodos();
        } catch (Exception exception) {
        }


        this.CFuentesDeDatos.removeAllItems();
        for (Object elem : c) {
            this.CFuentesDeDatos.addItem(elem);
        }
        this.CFuentesDeDatos.addItem("<Master>");
        setSize(720, 583);
    }

    private void setMensajeSalida(String msg) {
        this.TMensajes.setText(new StringBuilder(this.TMensajes.getText()).append(msg + "\n\n").toString());
    }

    private void setMensajeEjecucion(String msg) {
        this.LMensajes.setText(msg);
    }

    private void setMensajeEjecucion(String msg, String ttips) {
        this.LMensajes.setText(msg);
        this.LMensajes.setToolTipText(ttips);
    }

    public JTextArea getTConsulta() {
        return TConsulta;
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        Export = new javax.swing.JPopupMenu();
        MExportarHC = new javax.swing.JMenuItem();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        CFuentesDeDatos = new javax.swing.JComboBox();
        jSplitPane1 = new javax.swing.JSplitPane();
        jPanel1 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        TConsulta = new javax.swing.JTextArea();
        BEjecutar = new javax.swing.JButton();
        LMensajes = new javax.swing.JLabel();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        jScrollPane2 = new javax.swing.JScrollPane();
        TDatos = new javax.swing.JTable();
        jScrollPane3 = new javax.swing.JScrollPane();
        TMensajes = new javax.swing.JTextArea();
        jMenuBar1 = new javax.swing.JMenuBar();
        jMenu1 = new javax.swing.JMenu();
        CTransaccional = new javax.swing.JCheckBoxMenuItem();

        Export.setFont(new java.awt.Font("Dialog", 0, 12)); // NOI18N

        MExportarHC.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.MExportarHC.text")); // NOI18N
        MExportarHC.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                MExportarHCActionPerformed(evt);
            }
        });
        Export.add(MExportarHC);

        setClosable(true);
        setIconifiable(true);
        setResizable(true);
        setTitle(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.title")); // NOI18N
        setVisible(true);

        jLabel1.setBackground(new java.awt.Color(254, 254, 254));
        jLabel1.setFont(new java.awt.Font("Dialog", 1, 12)); // NOI18N
        jLabel1.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabel1.setIcon(new javax.swing.ImageIcon(getClass().getResource("/ig/formato/util/images/ig-formato.gif"))); // NOI18N
        jLabel1.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.jLabel1.text")); // NOI18N
        jLabel1.setBorder(javax.swing.BorderFactory.createEtchedBorder());
        jLabel1.setOpaque(true);

        jLabel2.setFont(new java.awt.Font("Dialog", 0, 10)); // NOI18N
        jLabel2.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.jLabel2.text")); // NOI18N

        CFuentesDeDatos.setFont(new java.awt.Font("Dialog", 0, 10)); // NOI18N
        CFuentesDeDatos.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        jSplitPane1.setDividerLocation(200);
        jSplitPane1.setDividerSize(7);
        jSplitPane1.setOrientation(javax.swing.JSplitPane.VERTICAL_SPLIT);
        jSplitPane1.setOneTouchExpandable(true);

        TConsulta.setColumns(20);
        TConsulta.setFont(new java.awt.Font("Monospaced", 0, 12)); // NOI18N
        TConsulta.setRows(5);
        TConsulta.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.TConsulta.text")); // NOI18N
        TConsulta.setToolTipText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.TConsulta.toolTipText")); // NOI18N
        TConsulta.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyReleased(java.awt.event.KeyEvent evt) {
                TConsultaKeyReleased(evt);
            }
        });
        jScrollPane1.setViewportView(TConsulta);

        BEjecutar.setFont(new java.awt.Font("Dialog", 0, 10)); // NOI18N
        BEjecutar.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.BEjecutar.text")); // NOI18N
        BEjecutar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                BEjecutarActionPerformed(evt);
            }
        });

        LMensajes.setFont(new java.awt.Font("Dialog", 1, 10)); // NOI18N
        LMensajes.setForeground(new java.awt.Color(0, 44, 201));

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addComponent(BEjecutar)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(LMensajes, javax.swing.GroupLayout.DEFAULT_SIZE, 416, Short.MAX_VALUE))
            .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 495, Short.MAX_VALUE)
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 170, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(BEjecutar)
                    .addComponent(LMensajes, javax.swing.GroupLayout.PREFERRED_SIZE, 21, javax.swing.GroupLayout.PREFERRED_SIZE)))
        );

        jSplitPane1.setTopComponent(jPanel1);

        jScrollPane2.setAutoscrolls(true);

        TDatos.setAutoCreateRowSorter(true);
        TDatos.setAutoResizeMode(javax.swing.JTable.AUTO_RESIZE_OFF);
        TDatos.setComponentPopupMenu(Export);
        jScrollPane2.setViewportView(TDatos);

        jTabbedPane1.addTab(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.jScrollPane2.TabConstraints.tabTitle"), jScrollPane2); // NOI18N

        TMensajes.setColumns(20);
        TMensajes.setFont(new java.awt.Font("Dialog", 0, 12)); // NOI18N
        TMensajes.setRows(5);
        jScrollPane3.setViewportView(TMensajes);

        jTabbedPane1.addTab(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.jScrollPane3.TabConstraints.tabTitle"), jScrollPane3); // NOI18N

        jSplitPane1.setRightComponent(jTabbedPane1);

        jMenu1.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.jMenu1.text")); // NOI18N

        CTransaccional.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_T, java.awt.event.InputEvent.CTRL_MASK));
        CTransaccional.setSelected(true);
        CTransaccional.setText(org.openide.util.NbBundle.getMessage(IQuery.class, "IQuery.CTransaccional.text")); // NOI18N
        jMenu1.add(CTransaccional);

        jMenuBar1.add(jMenu1);

        setJMenuBar(jMenuBar1);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jSplitPane1, javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addComponent(jLabel2)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(CFuentesDeDatos, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 44, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(CFuentesDeDatos, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 15, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jSplitPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 315, Short.MAX_VALUE)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents
    private void BEjecutarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BEjecutarActionPerformed
        new ejecutaScript().start();
        
}//GEN-LAST:event_BEjecutarActionPerformed

    private void TConsultaKeyReleased(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_TConsultaKeyReleased
        // TODO add your handling code here:        
        if (BEjecutar.isEnabled() && evt.getKeyCode() == java.awt.event.KeyEvent.VK_F5) {
            new ejecutaScript().start();
        }
    }//GEN-LAST:event_TConsultaKeyReleased

    private void MExportarHCActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_MExportarHCActionPerformed
        try {
            ig.formato.util.ExcelExporter.exportTable(this.TDatos, this,"iQuery");
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }//GEN-LAST:event_MExportarHCActionPerformed

    class ejecutaScript extends Thread {

        public ejecutaScript() {

        }

        @Override
        public void run() {
            Connection c = null;
            LMensajes.setForeground(new java.awt.Color(0, 44, 201));
            Statement statement = null;
            try {
                CTransaccional.setEnabled(false);
                BEjecutar.setEnabled(false);
                super.run();
                if (CFuentesDeDatos.getSelectedItem() != null) {
                    if (CFuentesDeDatos.getSelectedItem() instanceof ConexionDTO) {
                        c = conexion.getConnection(((ConexionDTO) CFuentesDeDatos.getSelectedItem()).getId());
                    } else if (CFuentesDeDatos.getSelectedItem() instanceof String) {
                        c = conexion.getConnectionIgFormato();
                    }
                }
                String scriptfull = TConsulta.getSelectedText();

                if (scriptfull == null || scriptfull.trim().equals("")) {
                    scriptfull = TConsulta.getText().trim();
                }

                String[] scripts = null;
                if (scriptfull.toLowerCase().trim().startsWith("file:")) {
                    scripts = scriptfull != null && scriptfull.length() > 0 ? scriptfull.split(":") : new String[0];
                } else {
                    scripts = scriptfull != null && scriptfull.length() > 0 ? scriptfull.split(";") : new String[0];
                }
                statement = c.createStatement();
                int cout = 0;                
                if (scripts[0].trim().equalsIgnoreCase("file")) {
//                    int count=0;
                    BufferedReader bufferde = new BufferedReader(new FileReader(new File(scripts[1].trim())));
                    String linea = null;
                    StringBuilder strb = new StringBuilder("");                    
                    while ((linea = bufferde.readLine()) != null) {
                        linea=linea.trim();
                        if (!linea.equals(";") && !linea.equals("GO")) {
                            if (linea.endsWith(";")) {                                
                                strb.append(linea.substring(0, linea.length() - 1));
                                setMensajeEjecucion("Procesando actualización " + (++cout));
//                                count++;
                                if(cout%2000 == 0){
                                    int[] row = statement.executeBatch();
                                    statement.clearBatch();
                                    setMensajeSalida(row.length+1 + " registros actualizados.");                                    
                                }else{
                                    statement.addBatch(strb.toString());
                                }
                                //Integer row = statement.executeUpdate(strb.toString());                                
                                strb = new StringBuilder("");
                            }else{
                                strb.append(linea.trim());
                            }
                        } else if (!strb.toString().trim().toUpperCase().startsWith("SELECT ")) {                            
                            setMensajeEjecucion("Procesando actualización " + (++cout));
                            //count++;
                            if(cout%2000 == 0){
                                int[] row = statement.executeBatch();
                                statement.clearBatch();                                
                                setMensajeSalida(row.length+1 + " registros actualizados.");
                            }else{
                                statement.addBatch(strb.toString());
                            }
                            //Integer row = statement.executeUpdate(strb.toString());
                            //setMensajeSalida(row.toString() + " registros actualizados.");
                            strb = new StringBuilder("");
                            //count++;
                        }
                    }
                    if (cout % 2000 != 0) {
                        int[] row = statement.executeBatch();
                        statement.clearBatch();                        
                        setMensajeSalida(row.length+1 + " registros actualizados.");                        
                    }
                    setMensajeEjecucion("Total registro Procesados " + (cout));
                } else {
                    for (int i = 0; i < scripts.length; i++) {
                        String string = scripts[i];
                        if (!string.trim().equals("")) {
                            //Statement statement = c.createStatement();
                            string = string.trim();
                            if (string.toUpperCase().startsWith("CREATE ") || string.toUpperCase().startsWith("ALTER ") || string.toUpperCase().startsWith("INSERT ") || string.toUpperCase().startsWith("UPDATE ") || string.toUpperCase().startsWith("DELETE ") || string.toUpperCase().startsWith("DROP ") || string.toUpperCase().startsWith("TRUNCATE ")) {
                                setMensajeEjecucion("Procesando actualizacion " + (i + 1) + " de " + scripts.length);
                                c.setAutoCommit(!CTransaccional.isSelected());
                                Integer row = statement.executeUpdate(string);
                                //statement.close();
                                setMensajeSalida(row.toString() + " registros actualizados.");
                            }
                        }
                    }
                    if (CTransaccional.isSelected() && !c.getAutoCommit()) {
                        c.commit();
                    }
                    c.setAutoCommit(true);

                    for (int i = 0; i < scripts.length; i++) {
                        String string = scripts[i];
                        if (!string.trim().equals("")) {
                            string = string.trim();
                            if (string.toUpperCase().startsWith("SELECT ")) {
                                setMensajeEjecucion("Procesando consulta " + (i + 1) + " de " + scripts.length);
                                ResultSet res = statement.executeQuery(string);
                                setResultado(res);
//                                try {
//                                    res.close();
//                                } catch (Exception exception) {
//                                }
                            }
                        }
                    }
                }
                setMensajeEjecucion((cout>0?cout:scripts.length) + " scripts procesados.");

            } catch (SQLException ex) {
                rollback(c);
                LMensajes.setForeground(new java.awt.Color(255, 21, 0));
                setMensajeEjecucion(ex.toString());
                ex.printStackTrace();
            } catch (ClassNotFoundException ex) {
                rollback(c);
                LMensajes.setForeground(new java.awt.Color(255, 21, 0));
                setMensajeEjecucion(ex.toString());
                ex.printStackTrace();
            } catch (IGFormatoException ex) {
                rollback(c);
                LMensajes.setForeground(new java.awt.Color(255, 21, 0));
                setMensajeEjecucion(ex.getSolucion());
                ex.printStackTrace();
            } catch (Exception ex) {
                rollback(c);
                LMensajes.setForeground(new java.awt.Color(255, 21, 0));
                setMensajeEjecucion(ex.toString());
                ex.printStackTrace();
            } finally {
                close(c);
                CTransaccional.setEnabled(true);
                BEjecutar.setEnabled(true);
                try {
                    statement.close();
                } catch (Exception exception) {
                }
            }
        }

        private void rollback(Connection c) {
            try {
                c.rollback();
            } catch (Exception ex) {
            //ex.printStackTrace();
            }
        }

        private void close(Connection c) {
            try {
                c.close();
            } catch (Exception exception) {
            }

        }

        private void setResultado(ResultSet RESULTADO) throws SQLException {
            int n = 0;
            n = RESULTADO.getMetaData().getColumnCount();
            String[] nombre = new String[n];
            for (int i = 1; i <= n; i++) {
                nombre[i - 1] = RESULTADO.getMetaData().getColumnLabel(i);
                int Tipo_Datos = RESULTADO.getMetaData().getColumnType(i);
            }
            Collection<Object[]> c = new Vector<Object[]>();
            while (RESULTADO.next()) {
                Object[] obj = new Object[n];
                for (int i = 1; i <= nombre.length; i++) {
                    obj[i - 1] = RESULTADO.getObject(i);
                }
                c.add(obj);
            }
            Object[][] data = new Object[c.size()][n];
            int i = 0;
            for (Object[] elem : c) {
                //            System.out.println("");
                for (int j = 0; j < n; j++) {
                    //                System.out.print(","+elem[j]);
                    if (elem[j] == null) {
                        data[i][j] = "(null)";
                    } else if (elem[j].getClass().getName().equals("java.sql.Date") || elem[j].getClass().getName().equals("java.util.Date") || elem[j].getClass().getName().equals("java.sql.Timestamp")) {
                        if (elem[j] instanceof java.sql.Date) {
                            data[i][j] = new Timestamp(((java.sql.Date) elem[j]).getTime()).toString().substring(0, 10);
                        } else if (elem[j] instanceof java.sql.Timestamp) {
                            data[i][j] = new Timestamp(((java.sql.Timestamp) elem[j]).getTime()).toString().substring(0, 10);
                        } else if (elem[j] instanceof java.util.Date) {
                            data[i][j] = new Timestamp(((java.util.Date) elem[j]).getTime()).toString().substring(0, 10);
                        }
                    } else {
                        data[i][j] = elem[j].toString();
                    }
                }
                i++;
            }
            boolean[] editables = new boolean[nombre.length];
            for (int j = 0; j < editables.length; j++) {
                editables[j] = false;
            }
            TDatos.setModel(new MyTableModel(data, nombre, editables));
            TDatos.updateUI();
        }
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton BEjecutar;
    private javax.swing.JComboBox CFuentesDeDatos;
    private javax.swing.JCheckBoxMenuItem CTransaccional;
    private javax.swing.JPopupMenu Export;
    private javax.swing.JLabel LMensajes;
    private javax.swing.JMenuItem MExportarHC;
    private javax.swing.JTextArea TConsulta;
    private javax.swing.JTable TDatos;
    private javax.swing.JTextArea TMensajes;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JMenu jMenu1;
    private javax.swing.JMenuBar jMenuBar1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JSplitPane jSplitPane1;
    private javax.swing.JTabbedPane jTabbedPane1;
    // End of variables declaration//GEN-END:variables
}
